/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.uoc.tdp.pac4.only5.manager.impl;

import edu.uoc.tdp.pac4.only5.domain.Booking;
import edu.uoc.tdp.pac4.only5.domain.EnumStateBooking;
import edu.uoc.tdp.pac4.only5.domain.Payment;
        
import edu.uoc.tdp.pac4.only5.exceptions.EespaisDatabaseException;
import edu.uoc.tdp.pac4.only5.manager.BookingManager;
import edu.uoc.tdp.pac4.only5.manager.DatabaseConnectionManager;
import edu.uoc.tdp.pac4.only5.vo.BookingPaymentVO;
import edu.uoc.tdp.pac4.only5.vo.UserBookingVO;
import edu.uoc.tdp.pac4.only5.tools.DateTool;

import java.rmi.RemoteException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Josep
 */
public class BookingManagerImpl extends AbstractManager implements BookingManager {
      
    public BookingManagerImpl(DatabaseConnectionManager databaseConnectionManager) throws RemoteException {
        super(databaseConnectionManager);
    }

    @Override
    public List<Booking> getBookings(String userId, Integer spaceId, Date from, Date to) throws RemoteException, EespaisDatabaseException{
         List<Booking> bookings = new ArrayList<Booking>();
        try {
            StringBuilder sql = new StringBuilder();
            
           getDatabaseConnectionManager().openConnection();
           PreparedStatement preparedStatement = null;
            if(spaceId != null && from != null && to != null) {
                sql.append("select * from bookings where userid=? and spaceid=? and record_date between ? and ?");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
                preparedStatement.setInt(2, spaceId);
                preparedStatement.setDate(3, new java.sql.Date(from.getTime()));
                preparedStatement.setDate(4, new java.sql.Date(to.getTime()));
            } else if(spaceId != null && from == null && to == null){
                sql.append("select * from bookings where userid=? and spaceid=?");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
                preparedStatement.setInt(2, spaceId);
            } else {
                sql.append("select * from bookings where userid=?");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
            } 
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                bookings.add(getBooking(resultSet));
            }
        } catch(SQLException ex){
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
        
       return bookings;
    }

    private Booking getBooking(ResultSet rs) throws SQLException { 
       Booking b = new Booking();
       b.setCancelTime(rs.getDate("cancel_time"));
       b.setCancelUser(rs.getString("cancel_user"));
       b.setCreationTime(rs.getDate("creation_time"));
       b.setCustomer(rs.getString("userid"));
       b.setEndTime(rs.getDate("end_time"));
       b.setExtraResources(null);
       b.setId(rs.getInt("bookingid"));
       b.setMadePayments(null);
       b.setPax(rs.getInt("pax"));
       b.setRecordDate(rs.getDate("record_date"));
       b.setSpace(rs.getInt("spaceid"));
       b.setStartTime(rs.getDate("start_time"));
       b.setStatus(rs.getString("status"));
       b.setTotalPrice(null);
       b.setCancelReason(rs.getString("cancel_reason"));
       return b;
    }

    @Override
    public Booking getBooking(int bookingId) throws RemoteException {
        Booking book=new Booking();
        try {
            StringBuilder sql = new StringBuilder();
            
           getDatabaseConnectionManager().openConnection();
           PreparedStatement preparedStatement = null;
            sql.append("select * from bookings where bookingid=? ");
            preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, bookingId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                book = getBooking(resultSet);
            }
        } catch (EespaisDatabaseException ex) {
            //TODO:Gerard, handle errors
            Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch(SQLException ex){
             Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        }        
        
       return book;
    }

    @Override
    public boolean isNewBookingPossible(Booking booking) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
        
    }

    @Override
    public void saveBooking(Booking booking) throws RemoteException, EespaisDatabaseException {
        try {
            getDatabaseConnectionManager().openConnection();
            PreparedStatement ps = getDatabaseConnectionManager().getConnection().prepareStatement("INSERT INTO bookings (userid,spaceid,start_time,end_time,pax,status,creation_time,record_date) VALUES (?,?,?,?,?,?,?,?)");            
            ps.setString(1, booking.getCustomer());
            ps.setInt(2, booking.getSpace());
            ps.setDate(3, DateTool.getInstance().getSqlDate(booking.getStartTime()));
            ps.setDate(4, DateTool.getInstance().getSqlDate(booking.getEndTime()));
            ps.setInt(5, booking.getPax());
            ps.setString(6, booking.getStatus());
            ps.setDate(7, DateTool.getInstance().getSqlDate(booking.getCreationTime()));
            ps.setDate(8, DateTool.getInstance().getSqlDate(booking.getRecordDate()));            
            ps.execute();
            
            booking.setId(getLastValueSequence());
        } catch (SQLException ex) {
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
    }
    
    @Override
    public Integer getLastValueSequence() throws EespaisDatabaseException, RemoteException {
        try {
            Integer result = null;
            getDatabaseConnectionManager().openConnection();
            PreparedStatement ps = getDatabaseConnectionManager().getConnection().prepareStatement("Select last_value from booking_seq");
            ResultSet rs = ps.executeQuery();
            if(rs != null && rs.next()){
                    result = rs.getInt(1);
            }
            return result;
        } catch (SQLException ex) {
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
    }

    @Override
    public void allocateBooking(Booking booking) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void cancelBooking(Booking booking) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<Booking> getNonAllocatedBookings() throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Double getCancellationFee(Booking booking) throws RemoteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    
    
    @Override
    /*
     * Rafael correal
     * Devuelve un list con los Bookings según parametros de búsqueda del
     * subsistema de aulas
     */
    public List<UserBookingVO> getBookingsVO(String userId, Integer spaceId, Date from, Date to,String estado) throws RemoteException{
         List<UserBookingVO> userBookingVO = new ArrayList<UserBookingVO>();
        try {
            StringBuilder sql = new StringBuilder();
            
           getDatabaseConnectionManager().openConnection();
           PreparedStatement preparedStatement = null;
            if(userId!= null && spaceId != null && estado !=null) {
                sql .append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                    .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                    .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                    .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                    .append(" where bookings.userid=? and bookings.spaceid=? " )
                    .append(" and status =? ")
                    .append(" and users.userid=bookings.userid") 
                    .append(" and spaces.spaceid=bookings.spaceid");
                
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
                preparedStatement.setInt(2, spaceId);
                preparedStatement.setString(3, estado);
            } else if(userId!= null && spaceId != null &&  estado==null){
                 sql .append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                     .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                     .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                     .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                     .append(" where bookings.userid=? and bookings.spaceid=? " )
                     .append(" and users.userid=bookings.userid")
                     .append(" and spaces.spaceid=bookings.spaceid");
                  preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                  preparedStatement.setString(1, userId);
                  preparedStatement.setInt(2, spaceId);
                 
            } else if(userId != null && spaceId == null && estado==null ){
                sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append(" where bookings.userid=?")
                   .append(" and users.userid=bookings.userid")
                   .append(" and spaces.spaceid=bookings.spaceid");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
                
            } else if(userId != null && spaceId == null && estado!=null ){
                sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append("  where bookings.userid=?")
                   .append(" and bookings.status= ? ")
                   .append(" and users.userid=bookings.userid")
                   .append(" and spaces.spaceid=bookings.spaceid");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, userId);
                preparedStatement.setString(2, estado);
            }else if(userId==null && spaceId == null && estado==null){
                 sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append(" where users.userid=bookings.userid") 
                   .append(" and spaces.spaceid=bookings.spaceid");
                   
               
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            }else  if(userId == null && spaceId == null && estado!=null ){
                sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append(" where bookings.status=?")
                   .append(" and users.userid=bookings.userid")
                   .append(" and spaces.spaceid=bookings.spaceid");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setString(1, estado );
               
            }else  if(userId == null && spaceId != null && estado==null ){
                sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append(" where bookings.spaceid=?")
                   .append(" and users.userid=bookings.userid")
                   .append(" and spaces.spaceid=bookings.spaceid");
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
                preparedStatement.setInt(1, spaceId);
               
            } else 
            {
                sql.append("select bookings.bookingid,bookings.userid,bookings.start_time,bookings.end_time,")
                   .append(" bookings.spaceid,bookings.pax,bookings.status,bookings.creation_time,bookings.cancel_time,")
                   .append(" bookings.cancel_user,bookings.record_date,users.userid,users.name,users.surname1,users.surname2,")
                   .append(" spaces.spaceid,spaces.spacedescription from bookings,users,spaces ")
                   .append(" where  users.userid=bookings.userid")
                   .append(" and spaces.spaceid=bookings.spaceid");
                  
                preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            }
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                userBookingVO.add(getBookingsVO(resultSet));
            }
        } catch (EespaisDatabaseException ex) {
           Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
            
        } catch(SQLException ex){
             Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
           
          
        }
        
       return userBookingVO;
    }
    /*
     * Devuelve la clasa UserBookingVo, rellenandolo con el rs
     * 
     */
    private   UserBookingVO getBookingsVO(ResultSet rs) throws SQLException {
      
       UserBookingVO b = new UserBookingVO();
        
       
       b.setCancelTime(rs.getDate("cancel_time"));
       b.setCancelUser(rs.getString("cancel_user"));
       b.setCreationTime(rs.getDate("creation_time"));
       b.setUserId(rs.getString("userid"));
       b.setEndTime(rs.getDate("end_time"));
       b.setBookingId(rs.getInt("bookingid"));
       b.setSpaceId(rs.getInt("spaceid"));
       b.setPax(rs.getInt("pax"));
       b.setRecordDate(rs.getDate("record_date"));
       b.setStartTime(rs.getDate("start_time"));
       b.setStatus(rs.getString("status"));
       b.setName(rs.getString("name"));
       b.setSurname1(rs.getString("surname1"));
       b.setSurname2(rs.getString("surname2"));
       b.setSpaceDescription(rs.getString("spaceDescription"));
       return b;       
    }
    
    /* Rafael correal
     * Update booking por cancelacion: Cauda,usuario y fecha 
     * @ param bookingId
     * @param causa
     * @param user
     */
    @Override
    public void UpdateCancelBooking(int bookingId, String causa,String userAdm) throws RemoteException, EespaisDatabaseException {
         
        
         Date fecha;
         StringBuilder sql = new StringBuilder();
            fecha= DateTool.getInstance().getSqlDate(new Date());
           
        try {
           String sql1 = null;
           sql1="UPDATE bookings  SET cancel_reason =' "+ causa +"',"  ;
           sql1 = sql1 + " status = '" + EnumStateBooking.CANCELADA + "'," ;
           sql1 = sql1 + " cancel_user = '" + userAdm +"'," ;
           sql1 = sql1 + " cancel_time = '" + fecha + "'";
           sql1 = sql1 + " WHERE bookingid=" + bookingId ;
            
           sql.append(sql1);
           
           getDatabaseConnectionManager().openConnection();
           PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
           preparedStatement.execute();
                       
           getDatabaseConnectionManager().closeConnection();
           ResultSet resultSet = preparedStatement.getResultSet();
 
        } catch(SQLException ex){
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
        

    }
    /** rafael correal
     * Funcion que calcula el recargo a aplicar cuando se cancela una reserva(booking)
     * 20% si falta una semana o mas para comenzar 
     * 50% si falta menos de una semana
     * 100% si esta en el periodo de desarrollo de la reserva
     * Modificamos la tabla payment
     * Devuelve el recargo a sumar a la tabla payment
     * @param booking 
     * @return recargo 
     */
    @Override
    public int calculaRecargo(Booking booking,Payment pay) throws RemoteException {
        long diferencia=0;
        int recargo=0;
        int total1=0;
        
        Date fecha;
        // fecha de hoy
        fecha= DateTool.getInstance().getSqlDate(new Date());
        // si la fecha del dia es inferior a la fecha final de la reserva
        
        if (fecha.before(booking.getEndTime()) && fecha.after(booking.getStartTime()))
        {
            // 100 % de recargo a caer dentro del periodo marcado
            recargo = pay.getPrice() * 2 ;
            
        }
         else
        {
            if (fecha.before(booking.getEndTime()))
            {
        
                if (fecha.before(booking.getStartTime()))
                {
                    diferencia=booking.getStartTime().getTime()- fecha.getTime();
                    if (diferencia > 8)
                    {
                        recargo=(pay.getPrice()*20)/100;    
                    }
                    else
                    {
                        recargo=(pay.getPrice()*50)/100;  
                    }
                    
                }   
            }
            
        }   
     return recargo;
    }

    @Override
    public List<BookingPaymentVO> getHistoricOfUser(String userId) throws RemoteException, EespaisDatabaseException {
        List<BookingPaymentVO> bookings = new ArrayList<BookingPaymentVO>();
        try {
            getDatabaseConnectionManager().openConnection();
            String sql = "select * from bookings inner join payments on bookings.bookingid = payments.bookingid where userid=?";
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql);
            preparedStatement.setString(1, userId);
            ResultSet rs = preparedStatement.executeQuery();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){                
                bookings.add(getBookingPaymentVO(rs));
            }
            
        } catch (SQLException ex) {
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        } 
        return bookings;
    }
    
    private BookingPaymentVO getBookingPaymentVO(ResultSet rs) throws SQLException{
        BookingPaymentVO bookingPaymentVO = new BookingPaymentVO();
        bookingPaymentVO.setBooking(getBooking(rs));
        bookingPaymentVO.setPayment(PaymentManagerImpl.getPayment(rs));
        return bookingPaymentVO;
    }
     public boolean comprobarBooking(Booking bo) throws RemoteException, SQLException, EespaisDatabaseException {
  
       boolean resultado=false;
       try
       {
         StringBuilder sql = new StringBuilder();
            
         getDatabaseConnectionManager().openConnection();
         PreparedStatement preparedStatement = null;
        sql .append("select * from bookings")
            .append(" where bookingid =? and bookings.start_time >=? " )
            .append(" and end_time <=? ")
            .append(" and status =? ");
                
        preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
        preparedStatement.setInt(1, bo.getId());
        preparedStatement.setDate(2, new java.sql.Date(bo.getStartTime().getTime()));
        preparedStatement.setDate(3, new java.sql.Date(bo.getEndTime().getTime()));
      
        preparedStatement.setString(3, bo.getStatus());
        preparedStatement.execute();
        getDatabaseConnectionManager().closeConnection();
        ResultSet resultSet = preparedStatement.getResultSet();
        while(resultSet.next())
        {                
                resultado = true;
        }
       } 
        catch (EespaisDatabaseException ex) 
        {
           Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
            
        } catch(SQLException ex)
        {
             Logger.getLogger(BookingManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
           
          
        }
       
       return resultado;
       
   }
    
}
